<?php
require_once 'vendor/autoload.php';

use Illuminate\Database\Capsule\Manager as Capsule;

$capsule = new Capsule;
$capsule->addConnection([
    'driver'    => 'mysql',
    'host'      => '129.226.194.102',
    'database'  => 'aimeos_lvgang_to',
    'username'  => 'aimeos_lvgang_to',
    'password'  => 'ZQaJw6X9nksn8kt7',
    'charset'   => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix'    => '',
]);

$capsule->setAsGlobal();
$capsule->bootEloquent();

try {
    $pdo = $capsule->getConnection()->getPdo();
    
    // Create mshop_catalog table
    $sql = "CREATE TABLE IF NOT EXISTS mshop_catalog (
        id INT AUTO_INCREMENT PRIMARY KEY,
        parentid INT DEFAULT NULL,
        level SMALLINT NOT NULL DEFAULT 0,
        code VARCHAR(64) NOT NULL,
        label VARCHAR(255) NOT NULL,
        url TEXT,
        config TEXT,
        nleft INT NOT NULL DEFAULT 1,
        nright INT NOT NULL DEFAULT 2,
        status SMALLINT NOT NULL DEFAULT 1,
        siteid VARCHAR(255) NOT NULL DEFAULT '',
        target VARCHAR(255) DEFAULT '',
        mtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        ctime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        editor VARCHAR(255) NOT NULL DEFAULT '',
        UNIQUE KEY unq_mscat_sid_code (siteid, code),
        KEY idx_mscat_nlt_nrt (nleft, nright),
        KEY idx_mscat_level_status (level, status),
        KEY fk_mscat_pid (parentid),
        CONSTRAINT fk_mscat_pid FOREIGN KEY (parentid) REFERENCES mshop_catalog (id) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    
    $pdo->exec($sql);
    echo "mshop_catalog table created successfully\n";
    
    // Create mshop_media_type table
    $sql = "CREATE TABLE IF NOT EXISTS mshop_media_type (
        id INT AUTO_INCREMENT PRIMARY KEY,
        siteid VARCHAR(255) NOT NULL DEFAULT '',
        domain VARCHAR(32) NOT NULL,
        code VARCHAR(64) NOT NULL,
        label VARCHAR(255) NOT NULL,
        pos INT NOT NULL DEFAULT 0,
        status SMALLINT NOT NULL DEFAULT 1,
        i18n TEXT,
        mtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        ctime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        editor VARCHAR(255) NOT NULL DEFAULT '',
        UNIQUE KEY unq_msmedty_sid_dom_code (siteid, domain, code),
        KEY idx_msmedty_status_pos (status, pos)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    
    $pdo->exec($sql);
    echo "mshop_media_type table created successfully\n";
    
    // Create mshop_media table
    $sql = "CREATE TABLE IF NOT EXISTS mshop_media (
        id INT AUTO_INCREMENT PRIMARY KEY,
        siteid VARCHAR(255) NOT NULL DEFAULT '',
        type VARCHAR(64) NOT NULL,
        langid VARCHAR(5) DEFAULT NULL,
        domain VARCHAR(32) NOT NULL,
        label VARCHAR(255) NOT NULL,
        link VARCHAR(255) NOT NULL,
        preview TEXT,
        mimetype VARCHAR(64) NOT NULL,
        filesync DATETIME DEFAULT NULL,
        status SMALLINT NOT NULL DEFAULT 1,
        mtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        ctime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        editor VARCHAR(255) NOT NULL DEFAULT '',
        KEY idx_msmed_sid_dom_langid (siteid, domain, langid),
        KEY idx_msmed_sid_dom_label (siteid, domain, label),
        KEY idx_msmed_sid_dom_mime (siteid, domain, mimetype),
        KEY idx_msmed_sid_dom_link (siteid, domain, link)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    
    $pdo->exec($sql);
    echo "mshop_media table created successfully\n";
    
    // Create mshop_text_type table
    $sql = "CREATE TABLE IF NOT EXISTS mshop_text_type (
        id INT AUTO_INCREMENT PRIMARY KEY,
        siteid VARCHAR(255) NOT NULL DEFAULT '',
        domain VARCHAR(32) NOT NULL,
        code VARCHAR(64) NOT NULL,
        label VARCHAR(255) NOT NULL,
        pos INT NOT NULL DEFAULT 0,
        status SMALLINT NOT NULL DEFAULT 1,
        i18n TEXT,
        mtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        ctime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        editor VARCHAR(255) NOT NULL DEFAULT '',
        UNIQUE KEY unq_mstexty_sid_dom_code (siteid, domain, code),
        KEY idx_mstexty_status_pos (status, pos)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    
    $pdo->exec($sql);
    echo "mshop_text_type table created successfully\n";
    
    // Create mshop_text table
    $sql = "CREATE TABLE IF NOT EXISTS mshop_text (
        id INT AUTO_INCREMENT PRIMARY KEY,
        siteid VARCHAR(255) NOT NULL DEFAULT '',
        type VARCHAR(64) NOT NULL,
        langid VARCHAR(5) DEFAULT NULL,
        domain VARCHAR(32) NOT NULL,
        label VARCHAR(255) NOT NULL,
        content LONGTEXT,
        status SMALLINT NOT NULL DEFAULT 1,
        mtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        ctime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        editor VARCHAR(255) NOT NULL DEFAULT '',
        KEY idx_mstex_sid_dom_langid (siteid, domain, langid),
        KEY idx_mstex_sid_dom_label (siteid, domain, label),
        KEY idx_mstex_sid_dom_cont (siteid, domain, content(255))
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    
    $pdo->exec($sql);
    echo "mshop_text table created successfully\n";
    
    // Insert default data for media types
    $insertSql = "INSERT IGNORE INTO mshop_media_type (siteid, domain, code, label, pos, status, editor) VALUES 
        ('', 'cms', 'default', 'Standard', 0, 1, 'setup'),
        ('1.', 'cms', 'default', 'Standard', 0, 1, 'setup'),
        ('', 'catalog', 'default', 'Standard', 0, 1, 'setup'),
        ('1.', 'catalog', 'default', 'Standard', 0, 1, 'setup'),
        ('', 'product', 'default', 'Standard', 0, 1, 'setup'),
        ('1.', 'product', 'default', 'Standard', 0, 1, 'setup');";
    
    $pdo->exec($insertSql);
    echo "Default media type data inserted successfully\n";
    
    // Insert default data for text types
    $insertSql = "INSERT IGNORE INTO mshop_text_type (siteid, domain, code, label, pos, status, editor) VALUES 
        ('', 'cms', 'default', 'Standard', 0, 1, 'setup'),
        ('1.', 'cms', 'default', 'Standard', 0, 1, 'setup'),
        ('', 'catalog', 'name', 'Name', 0, 1, 'setup'),
        ('1.', 'catalog', 'name', 'Name', 0, 1, 'setup'),
        ('', 'product', 'name', 'Name', 0, 1, 'setup'),
        ('1.', 'product', 'name', 'Name', 0, 1, 'setup');";
    
    $pdo->exec($insertSql);
    echo "Default text type data inserted successfully\n";
    
    // Insert default catalog root node
    $insertSql = "INSERT IGNORE INTO mshop_catalog (code, label, nleft, nright, level, status, siteid, editor) VALUES 
        ('home', 'Home', 1, 2, 0, 1, '1.', 'setup');";
    
    $pdo->exec($insertSql);
    echo "Default catalog data inserted successfully\n";
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}